﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;

namespace WebApplication1
{
    public partial class WebForm8 : System.Web.UI.Page
    {
        private OleDbConnection accessconn;
        protected void Page_Load(object sender, EventArgs e)
        {
            accessconn = new OleDbConnection();
            accessconn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                @"C:\Dbase_SEA\myDB.accdb";
        }

        protected void home_Click(object sender, EventArgs e)
        {
            Response.Redirect("http://localhost:62505/Default.aspx");
        }

        private void MessageBox(string msg)
        {
            Label lbl = new Label();
            lbl.Text = "<script language='javascript'>" + Environment.NewLine + "window.alert('" + msg + "')</script>";
            Page.Controls.Add(lbl);
        }
        //Calculates the total revenue for the given dates
        protected void totalrevenue_Click(object sender, EventArgs e)
        {
            accessconn.Open();
            string date1 = Calendar1.SelectedDate.Date.ToString();
            string date2 = Calendar2.SelectedDate.Date.ToString();
            DataSet ds = new DataSet();
            string query11 = "select Amount from FoodOrder where OrderDate between #"+date1+"# and #"+date2+"#;";
            OleDbDataAdapter da = new OleDbDataAdapter(query11,accessconn);
            da.Fill(ds);
            if (date1 != "1/1/0001 12:00:00 AM" && date2 != "1/1/0001 12:00:00 AM") //Checks whether a date is selected from the Calendar.
            {
                double tot_amount = 0;
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    double amount = Convert.ToDouble(ds.Tables[0].Rows[i]["Amount"].ToString());
                    tot_amount += amount;
                }
                TextBox1.Text = "$"+Convert.ToString(tot_amount);
            }
            else MessageBox("Select a Date");
            accessconn.Close();
        }

        //Best server is the one who made most number of orders
        protected void bestserver_Click(object sender, EventArgs e)
        {
            accessconn.Open();
            string date1 = Calendar1.SelectedDate.Date.ToString();
            string date2 = Calendar2.SelectedDate.Date.ToString();
            DataSet ds = new DataSet();
            string query12 = "select Ename from Employee where Eid = (select Eid from FoodOrder where OrderDate between #" + date1 + "# and #" + date2 + "# group by Eid having Count(*) = (select MAX(emporder) from (select Count(*) as emporder from FoodOrder where OrderDate between #" + date1 + "# and #" + date2 + "# group by Eid)));";
            OleDbDataAdapter da = new OleDbDataAdapter(query12,accessconn);
            da.Fill(ds);
            if (date1 != "1/1/0001 12:00:00 AM" && date2 != "1/1/0001 12:00:00 AM") //Checks whether a date is selected from the Calendar.
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    TextBox2.Text = ds.Tables[0].Rows[0]["Ename"].ToString();
                }
                else TextBox2.Text = "--No Data Available--";
            }
            else MessageBox("Select a Date");
            accessconn.Close();
        }

        //calculates the most sold item between the specified dates
        protected void mostsolditem_Click(object sender, EventArgs e)
        {
            accessconn.Open();
            string date1 = Calendar1.SelectedDate.Date.ToString();
            string date2 = Calendar2.SelectedDate.Date.ToString();
            DataSet ds = new DataSet();
            string query13 = "select ItemName from FoodItem where ItemEntry IN (select ItemEntry from ListOfItems, FoodOrder where ListOfItems.Orderid=FoodOrder.Orderid and OrderDate between #" + date1 + "# and #" + date2 + "# group by ItemEntry having Count(*) IN (select MAX(item) from (select Count(*) as item from ListOfItems, FoodOrder where ListOfItems.Orderid=FoodOrder.Orderid and OrderDate between #" + date1 + "# and #" + date2 + "# group by ItemEntry)))";
            OleDbDataAdapter da = new OleDbDataAdapter(query13, accessconn);
            da.Fill(ds);
            if (date1 != "1/1/0001 12:00:00 AM" && date2 != "1/1/0001 12:00:00 AM") //Checks whether a date is selected from the Calendar.
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    TextBox3.Text = ds.Tables[0].Rows[0]["ItemName"].ToString();
                }
                else TextBox3.Text = "--No Data Available--";
            }
            else MessageBox("Select a Date");
            accessconn.Close();
        }

        //calculates the least sold item between the specified dates
        protected void leastsolditem_Click(object sender, EventArgs e)
        {
            accessconn.Open();
            string date1 = Calendar1.SelectedDate.Date.ToString();
            string date2 = Calendar2.SelectedDate.Date.ToString();
            DataSet ds = new DataSet();
            string query14 = "select ItemName from FoodItem where ItemEntry IN (select ItemEntry from ListOfItems, FoodOrder where ListOfItems.Orderid=FoodOrder.Orderid and OrderDate between #" + date1 + "# and #" + date2 + "# group by ItemEntry having Count(*) IN (select MIN(item) from (select Count(*) as item from ListOfItems, FoodOrder where ListOfItems.Orderid=FoodOrder.Orderid and OrderDate between #" + date1 + "# and #" + date2 + "# group by ItemEntry)))";
            OleDbDataAdapter da = new OleDbDataAdapter(query14, accessconn);
            da.Fill(ds);
            if (date1 != "1/1/0001 12:00:00 AM" && date2 != "1/1/0001 12:00:00 AM") //Checks whether a date is selected from the Calendar.
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    TextBox4.Text = ds.Tables[0].Rows[0]["ItemName"].ToString();
                }
                else TextBox4.Text = "--No Data Available--";
            }
            else MessageBox("Select a Date");
            accessconn.Close();
        }

        //calculates the number of carry-out orders made between the specified dates
        //Determined by entering 0 in the number of customers in the menu order page
        protected void carryout_Click(object sender, EventArgs e)
        {
            accessconn.Open();
            string date1 = Calendar1.SelectedDate.Date.ToString();
            string date2 = Calendar2.SelectedDate.Date.ToString();
            DataSet ds = new DataSet();
            string query15 = "select Count(*) as CarryOut from FoodOrder where CustomerCount=0 and OrderDate between #" + date1 + "# and #" + date2 + "#";
            OleDbDataAdapter da = new OleDbDataAdapter(query15, accessconn);
            da.Fill(ds);
            if (date1 != "1/1/0001 12:00:00 AM" && date2 != "1/1/0001 12:00:00 AM") //Checks whether a date is selected from the Calendar.
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    TextBox5.Text = ds.Tables[0].Rows[0]["CarryOut"].ToString();
                }
                else TextBox5.Text = "--No Data Available--";
            }
            else MessageBox("Select a Date");
            accessconn.Close();
        }

        //Calculates the total number of orders made between the specified dates
        protected void totalorders_Click(object sender, EventArgs e)
        {
            accessconn.Open();
            string date1 = Calendar1.SelectedDate.Date.ToString();
            string date2 = Calendar2.SelectedDate.Date.ToString();
            DataSet ds = new DataSet();
            string query16 = "select Count(*) as TOT_orders from FoodOrder where OrderDate between #" + date1 + "# and #" + date2 + "#";
            OleDbDataAdapter da = new OleDbDataAdapter(query16, accessconn);
            da.Fill(ds);
            if (date1 != "1/1/0001 12:00:00 AM" && date2 != "1/1/0001 12:00:00 AM") //Checks whether a date is selected from the Calendar.
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    TextBox6.Text = ds.Tables[0].Rows[0]["TOT_orders"].ToString();
                }
                else TextBox6.Text = "--No Data Available--";
            }
            else MessageBox("Select a Date");
            accessconn.Close();
        }

        //displays the birthdays of employees for a particular month
        protected void birthdays_Click(object sender, EventArgs e)
        {
            accessconn.Open();
            string date1 = Calendar1.SelectedDate.Date.ToString();
            string date2 = Calendar2.SelectedDate.Date.ToString();
            DateTime d1 = new DateTime();
            DataSet ds = new DataSet();
            string query17 = "select Ename, DOB from Employee";
            OleDbDataAdapter da = new OleDbDataAdapter(query17, accessconn);
            da.Fill(ds);
            if (date1 != "1/1/0001 12:00:00 AM" && date2 != "1/1/0001 12:00:00 AM") //Checks whether a date is selected from the Calendar.
            {
                if (Calendar1.SelectedDate.Month.ToString() == Calendar2.SelectedDate.Month.ToString())
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        d1 = Convert.ToDateTime(ds.Tables[0].Rows[i]["DOB"].ToString());
                        string month = d1.Month.ToString();
                        if (month == Calendar1.SelectedDate.Month.ToString())
                        {
                            ListItem item = new ListItem();
                            item.Text = ds.Tables[0].Rows[i]["Ename"].ToString()+"("+ds.Tables[0].Rows[i]["DOB"].ToString()+")";
                            ListBox1.Items.Add(item);
                        }
                    }
                }
                else MessageBox("Select a particular Month");
            }
            else MessageBox("Select a Date");
            accessconn.Close();

        }
    }
}
